using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//
using System.Data;
using System.Data.SqlClient;
using DbFrame.Class;
using System.Data.Common;
using MySql.Data.MySqlClient;

namespace DbFrame.AdoDotNet
{
    public class DbHelper : IDbHelper, IDataBase
    {
        private string _ConnectionString { get; set; }
        private DataBaseType _DbType { get; set; }

        public DbHelper(string ConnectionString, DataBaseType dbtype)
        {
            this._ConnectionString = ConnectionString;
            _DbType = dbtype;
        }

        public bool Commit(List<SQL> li)
        {
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).Commit(li);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).Commit(li);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public bool Commit(List<SQL> li, Action<int, SQL, System.Data.Common.DbCommand> callBack)
        {
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).Commit(li, callBack);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).Commit(li, callBack);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public int ExecuteNonQuery(string strSql)
        {
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteNonQuery(strSql);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteNonQuery(strSql);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public int ExecuteNonQuery(string strSql, params System.Data.Common.DbParameter[] dbParameter)
        {
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteNonQuery(strSql, dbParameter);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteNonQuery(strSql, dbParameter);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public int ExecuteNonQuery(SQL sql)
        {
            var dbParameter = GetParameter(sql);
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteNonQuery(sql.Sql_Parameter, dbParameter.ToArray());
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteNonQuery(sql.Sql_Parameter, dbParameter.ToArray());
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public DataSet ExecuteDataset(string strSql)
        {
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteDataset(strSql);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteDataset(strSql);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public DataSet ExecuteDataset(string strSql, params System.Data.Common.DbParameter[] dbParameter)
        {
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteDataset(strSql, dbParameter);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteDataset(strSql, dbParameter);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public DataSet ExecuteDataset(SQL sql)
        {
            var dbParameter = GetParameter(sql);
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteDataset(sql.Sql_Parameter, dbParameter.ToArray());
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteDataset(sql.Sql_Parameter, dbParameter.ToArray());
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        /// <summary>
        /// 获取 DataTable
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string strSql)
        {
            var tabs = this.ExecuteDataset(strSql).Tables;
            if (tabs.Count > 0)
                return tabs[0];
            return null;
        }
        /// <summary>
        /// 获取 DataTable
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="dbParameter"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string strSql, params System.Data.Common.DbParameter[] dbParameter)
        {
            var tabs = this.ExecuteDataset(strSql, dbParameter).Tables;
            if (tabs.Count > 0)
                return tabs[0];
            return null;
        }

        /// <summary>
        /// 获取 DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(SQL sql)
        {
            var tabs = this.ExecuteDataset(sql).Tables;
            if (tabs.Count > 0)
                return tabs[0];
            return null;
        }

        public object ExecuteScalar(string strSql)
        {
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteScalar(strSql);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteScalar(strSql);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public object ExecuteScalar(string strSql, params System.Data.Common.DbParameter[] dbParameter)
        {
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteScalar(strSql, dbParameter);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteScalar(strSql, dbParameter);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public object ExecuteScalar(SQL sql)
        {
            var dbParameter = GetParameter(sql);
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteScalar(sql.Sql_Parameter, dbParameter);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteScalar(sql.Sql_Parameter, dbParameter);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public int ExecuteByProc(string procName)
        {
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteByProc(procName);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteByProc(procName);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public int ExecuteByProc(string procName, System.Data.Common.DbParameter[] dbParameter)
        {
            if (_DbType == DataBaseType.MsSql)
                return new MSSqlDatabase(_ConnectionString).ExecuteByProc(procName, dbParameter);
            else if (_DbType == DataBaseType.MySql)
                return new MySqlDatabase(_ConnectionString).ExecuteByProc(procName, dbParameter);
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        public int ExecuteByProc(SQL sql)
        {
            throw new NotImplementedException("暂未实现！");
        }

        public DataTable FindTable(string strSql, DbParameter[] dbParameter, string orderField, bool isAsc, int pageSize, int pageIndex, out int total)
        {
            if (_DbType == DataBaseType.MsSql)
            {
                return new MSSqlDatabase(_ConnectionString).FindTable(strSql, dbParameter, orderField, isAsc, pageSize, pageIndex, out total);
            }
            else if (_DbType == DataBaseType.MySql)
            {
                return new MySqlDatabase(_ConnectionString).FindTable(strSql, dbParameter, orderField, isAsc, pageSize, pageIndex, out total);
            }
            else if (_DbType == DataBaseType.Oracle)
                throw new Exception("暂不支持改数据库类型！");
            else
                throw new Exception("暂不支持改数据库类型！");
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="dbParameter"></param>
        /// <param name="orderField"></param>
        /// <param name="isAsc"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public PagingEntity PagingList(string strSql, DbParameter[] dbParameter, string orderField, bool isAsc, int pageIndex, int pageSize)
        {
            var pe = new PagingEntity();
            int PageCount = 0, Counts = 0;
            var dt = new DataTable();

            dt = this.FindTable(strSql, dbParameter, orderField, isAsc, pageSize, pageIndex, out Counts);

            pe.dt = dt;
            pe.Counts = Counts;

            pe.List = ConvertDataTableToList<Dictionary<string, object>>(dt);

            if (Counts % pageSize == 0)
                PageCount = Counts / pageSize;
            else
                PageCount = Counts / pageSize + 1;

            pe.PageCount = PageCount;

            return pe;
        }

        /// <summary>
        /// 获取Number
        /// </summary>
        /// <param name="TabName"></param>
        /// <param name="FieldNum"></param>
        /// <returns></returns>
        public int GetNumber(string TabName, string FieldNum)
        {
            var sql = string.Empty;
            switch (_DbType)
            {
                case DataBaseType.MsSql: sql = " exec getnumber '" + FieldNum + "','" + TabName + "'";
                    break;
                case DataBaseType.MySql: sql = " call getnumber ('" + FieldNum + "','" + TabName + "') ";
                    break;
                default:
                    throw new Exception("暂不支持改数据库类型！");
                    break;
            }

            return this.ExecuteScalar(sql).To_Int();
        }

        /// <summary>
        /// 将datatable转换为list<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <returns></returns>
        public static List<T> ConvertDataTableToList<T>(DataTable table)
        {
            var list = new List<T>();
            if (typeof(T).BaseType == typeof(BaseEntity))
            {
                var Model = (T)Activator.CreateInstance(typeof(T));
                var propertyInfo = Model.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
                foreach (DataRow dr in table.Rows)
                {
                    Model = (T)Activator.CreateInstance(typeof(T));
                    foreach (var item in propertyInfo)
                    {
                        string AttrName = item.Name;
                        foreach (DataColumn dc in dr.Table.Columns)
                        {
                            if (!AttrName.Equals(dc.ColumnName)) continue;
                            if (dr[dc.ColumnName] != DBNull.Value)
                                item.SetValue(Model, dr[dc.ColumnName], null);
                            else
                                item.SetValue(Model, null, null);
                        }
                    }
                    list.Add(Model);
                }
                return list;
            }

            //针对非 实体类型
            foreach (DataRow dr in table.Rows)
            {
                var model = new Dictionary<string, object>();
                foreach (DataColumn dc in table.Columns)
                {
                    if (dc.DataType.FullName == "MySql.Data.Types.MySqlDateTime")
                        model.Add(dc.ColumnName, dr[dc.ColumnName] == DBNull.Value ? null : dr[dc.ColumnName].To_DateTimeNull());
                    else if (dc.DataType.Equals(typeof(DateTime)))
                        model.Add(dc.ColumnName, (dr[dc.ColumnName] == DBNull.Value || dr[dc.ColumnName] == null ? "" : Convert.ToDateTime(dr[dc.ColumnName]).ToString("yyyy-MM-dd HH:mm:ss")));
                    else
                        model.Add(dc.ColumnName, dr[dc.ColumnName] == DBNull.Value ? null : dr[dc.ColumnName]);
                }
                var json = model.SerializeObject();
                json = System.Text.RegularExpressions.Regex.Replace(json, @"\\/Date\((\d+)\)\\/", match =>
                {
                    DateTime dt = new DateTime(1970, 1, 1);
                    dt = dt.AddMilliseconds(long.Parse(match.Groups[1].Value));
                    dt = dt.ToLocalTime();
                    return dt.ToString("yyyy-MM-dd HH:mm:ss");
                });
                list.Add(ModelExFunc.DeserializeObject<T>(json));
            }
            return list;
        }

        /// <summary>
        /// 转换实体
        /// </summary>
        /// <param name="r"></param>
        /// <param name="Model"></param>
        /// <returns></returns>
        public static T ToModel<T>(DataRow r) where T : BaseEntity, new()
        {
            var Model = (T)Activator.CreateInstance(typeof(T));
            var propertyInfo = Model.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (propertyInfo.Length <= 0) return Model;

            foreach (var item in propertyInfo)
            {
                string AttrName = item.Name;
                foreach (DataColumn dc in r.Table.Columns)
                {
                    if (!AttrName.Equals(dc.ColumnName)) continue;
                    if (r[dc.ColumnName] != DBNull.Value)
                        item.SetValue(Model, r[dc.ColumnName], null);
                }
            }
            return Model;
            //JavaScriptSerializer jss = new JavaScriptSerializer();
            //var model = new Dictionary<string, object>();
            //foreach (DataColumn item in r.Table.Columns)
            //{
            //    if (item.DataType.FullName == "MySql.Data.Types.MySqlDateTime")
            //        model.Add(item.ColumnName, r[item.ColumnName] == DBNull.Value ? null : r[item.ColumnName].To_DateTimeNull());
            //    else
            //        model.Add(item.ColumnName, r[item.ColumnName] == DBNull.Value ? null : r[item.ColumnName]);
            //}
            //var json = jss.Serialize(model);
            //json = System.Text.RegularExpressions.Regex.Replace(json, @"\\/Date\((\d+)\)\\/", match =>
            //{
            //    DateTime dt = new DateTime(1970, 1, 1);
            //    dt = dt.AddMilliseconds(long.Parse(match.Groups[1].Value));
            //    dt = dt.ToLocalTime();
            //    return dt.ToString("yyyy-MM-dd HH:mm:ss");
            //});
            //return jss.Deserialize<T>(json);
        }

        private DbParameter[] GetParameter(SQL sql)
        {
            var dbParameter = new List<DbParameter>();
            if (_DbType == DataBaseType.MsSql)
            {
                foreach (var item in sql.Parameter)
                {
                    dbParameter.Add(new SqlParameter() { ParameterName = item.Key, Value = item.Value == null ? DBNull.Value : item.Value });
                }
                return dbParameter.ToArray();
            }
            else if (_DbType == DataBaseType.MySql)
            {
                foreach (var item in sql.Parameter)
                {
                    dbParameter.Add(new MySqlParameter() { ParameterName = item.Key, Value = item.Value == null ? DBNull.Value : item.Value });
                }
                return dbParameter.ToArray();
            }
            else if (_DbType == DataBaseType.Oracle)
            {
                throw new Exception("暂不支持改数据库类型！");
            }
            else
            {
                throw new Exception("暂不支持改数据库类型！");
            }
        }


    }
}
